﻿using Model;
using Model.Medicine;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class MedicineDA : DBConnection
    {
        public MedicineDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
                
        
        public int addMedicine(Medicine med)
        {
            string query = "insert into " + Common.THUOC +
                " values(@medicinename,@medicinestrength,@routeofadministrationid,@medicineunitid,@medicinegroupid, @quantitytotal, @status); select SCOPE_IDENTITY();";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicinename", med.getMedicineName());
            sqlCommand.Parameters.AddWithValue("@medicinestrength", med.getStrength());
            sqlCommand.Parameters.AddWithValue("@routeofadministrationid", med.getRouteOfAdministrationId());
            sqlCommand.Parameters.AddWithValue("@medicineunitid", med.getMedUnitId());
            sqlCommand.Parameters.AddWithValue("@medicinegroupid", med.getMedGroupId());
            sqlCommand.Parameters.AddWithValue("@quantitytotal", med.getQuantityTotal());
            sqlCommand.Parameters.AddWithValue("@status", med.getStatus());
            try
            {
                open();
                int idRe = -1;
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }
        }

        public bool updateMedicine(Medicine med, bool isUpdateQuantity)
        {
            //log here
            int medId = med.getMedId();
            string query = "";

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicinename", med.getMedicineName());
            sqlCommand.Parameters.AddWithValue("@medicinestrength", med.getStrength());
            sqlCommand.Parameters.AddWithValue("@routeofadministrationid", med.getRouteOfAdministrationId());
            sqlCommand.Parameters.AddWithValue("@medicineunitid", med.getMedUnitId());
            sqlCommand.Parameters.AddWithValue("@medicinegroupid", med.getMedGroupId());
            if (isUpdateQuantity)
            {
                query = "update " + Common.THUOC +
                " set medicinename = @medicinename, " +
                "medicinestrength = @medicinestrength,routeofadministrationid = @routeofadministrationid" +
                ",medicineunitid = @medicineunitid,medicinegroupid = @medicinegroupid,@quantitytotal, status  = @status " +
                "where medicineid=" + medId;
                sqlCommand.Parameters.AddWithValue("@quantitytotal", med.getQuantityTotal());
            }
            else
            {
                query = "update " + Common.THUOC +
                " set medicinename = @medicinename, " +
                "medicinestrength = @medicinestrength,routeofadministrationid = @routeofadministrationid" +
                ",medicineunitid = @medicineunitid,medicinegroupid = @medicinegroupid, status  = @status " +
                "where medicineid=" + medId;
            }
            sqlCommand.Parameters.AddWithValue("@status", med.getStatus());

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                return false;
            }

        }
        /// <summary>
        /// Update current medicine anyway, even it is used. Only use this when you want to update name and strength.
        /// </summary>
        /// <param name="med"></param>
        /// <returns></returns>
        public bool updateMedicineAnyWay(Medicine med)
        {
            //log here
            int medId = med.getMedId();
            string query = "update " + Common.THUOC +
                " set medicinename = @medicinename, " +
                "medicinestrength = @medicinestrength,routeofadministrationid = @routeofadministrationid" +
                ",medicineunitid = @medicineunitid,medicinegroupid = @medicinegroupid " +
                "where medicineid=" + medId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicinename", med.getMedicineName());
            sqlCommand.Parameters.AddWithValue("@medicinestrength", med.getStrength());
            sqlCommand.Parameters.AddWithValue("@routeofadministrationid", med.getRouteOfAdministrationId());
            sqlCommand.Parameters.AddWithValue("@medicineunitid", med.getMedUnitId());
            sqlCommand.Parameters.AddWithValue("@medicinegroupid", med.getMedGroupId());

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                return false;
            }

        }

        /// <summary>
        /// Check if a medicine is used by patient
        /// </summary>
        /// <param name="medId"></param>
        /// <returns></returns>
        public bool isUsed(int medId)
        {
            string query = "select top 1 medicineid from " + Common.CHI_TIET_KHAM_BENH +
                " where medicineid = " + medId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return true;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return true;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        
        /// <summary>
        /// Update new quantity
        /// </summary>
        /// <param name="medId">Id of medicine will be updated</param>
        /// <param name="quantity"> A new value will be added to existing total of quantity</param>
        /// <returns></returns>
        public bool updateQuantityTotal(int medId, int quantity)
        {
            //log here
            //
            int newTotal = getQuanityTotal(medId) + quantity;
            string query = "update " + Common.THUOC +
                " set medicinename = @medicinename, " +
                " quantitytotal = " + newTotal+
                " where medicineid=" + medId;

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                return false;
            }

        }

        public int getQuanityTotal(int medId)
        {
            string query = "select quantitytotal from " + Common.THUOC +
                " where medicineid = " + medId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return sqlDataReader.GetInt32(0);

                }
                return 0;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
            }
        }
        
        public Medicine getMedicineById(int medId)
        {
            string query = "select * from " + Common.THUOC + " where id=" + medId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Medicine med = new Medicine();
                while (sqlDataReader.Read())
                {
                    med = createMedicineFromReader(sqlDataReader);

                }
                close();
                return med;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private Medicine createMedicineFromReader(SqlDataReader rd)
        {
            if (rd != null && !rd.IsClosed)
            {
                Medicine med = new Medicine();
                med.setMedId(rd.GetInt32(0));
                med.setMedicineName(rd.GetString(1));
                med.setStrength(rd.GetString(2));
                med.setRouteOfAdministrationId(rd.GetInt32(3));
                med.setMedUnitId(rd.GetInt32(4));
                med.setMedGroupId(rd.GetInt32(5));
                med.setQuantityTotal(rd.GetInt32(6));
                med.setStatus(rd.GetInt16(7));
                return med;
            }
            else
            {
                return null;
            }

        }

        public Medicine[] getAllMedicine()
        {
            string query = "select * from " + Common.THUOC;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Medicine> liMed = new List<Medicine>();
                Medicine med = new Medicine();
                while (sqlDataReader.Read())
                {
                    med = createMedicineFromReader(sqlDataReader);
                    liMed.Add(med);
                }
                close();
                return liMed.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        /////////////////////////////////////////
        //
        // Medicine Unit
        //
        /////////////////////////////////////////

        public bool addMedUnit(MedUnit medUnit)
        {
            string query = "insert into " + Common.DON_VI_THUOC +
                " values(@medicineunitname)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicineunitname", medUnit.getMedUnitName());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }

        }

        public MedUnit[] getAllMedUnit()
        {
            string query = "select * from " + Common.DON_VI_THUOC;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<MedUnit> liMedUnit = new List<MedUnit>();
                //MedUnit medUnit = new MedUnit();
                MedUnit medUnit ;

                while (sqlDataReader.Read())
                {
                    medUnit = new MedUnit();
                    medUnit.setMedUnitId(sqlDataReader.GetInt32(0));
                    medUnit.setMedUnitName(sqlDataReader.GetString(1));
                    liMedUnit.Add(medUnit);
                }
                close();
                return liMedUnit.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        /// <summary>
        /// Get medicine unit of a medicine
        /// </summary>
        /// <param name="medId">Medicine Unit Id</param>
        /// <returns></returns>
        public MedUnit getMedUnit(int medUnitId)
        {
            string query = "select * from " + Common.DON_VI_THUOC +
                " where medicineunitid= " +medUnitId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                MedUnit medUnit = new MedUnit();
                while (sqlDataReader.Read())
                {
                    medUnit.setMedUnitId(sqlDataReader.GetInt32(0));
                    medUnit.setMedUnitName(sqlDataReader.GetString(1));
                }
                close();
                return medUnit;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isMedUnitExist(int medUnitId)
        {
            string query = "select medicineunitid from " + Common.DON_VI_THUOC +
                " where medicineunitid = " + medUnitId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isMedUnitNameExist(string name)
        {
            string query = "select medicineunitname from " + Common.DON_VI_THUOC +
                " where medicineunitname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        /////////////////////////////////////////
        //
        // Medicine Group
        //
        /////////////////////////////////////////

        public bool addMedGroup(MedGroup medGroup)
        {
            string query = "insert into " + Common.PHAN_NHOM_THUOC +
                " values(@medicinegroupname,@note)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicinegroupname", medGroup.getMedGroupName());
            sqlCommand.Parameters.AddWithValue("@note", medGroup.getMedGroupNote());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }

        }

        public MedGroup[] getAllMedGroup()
        {
            string query = "select * from " + Common.PHAN_NHOM_THUOC;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<MedGroup> liMedGroup = new List<MedGroup>();
                MedGroup medGroup = null;
                while (sqlDataReader.Read())
                {
                    medGroup = new MedGroup();
                    medGroup.setMedGroupId(sqlDataReader.GetInt32(0));
                    medGroup.setMedGroupName(sqlDataReader.GetString(1));
                    medGroup.setMedGroupNote(sqlDataReader.GetString(2));
                    liMedGroup.Add(medGroup);
                }
                close();
                return liMedGroup.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        /// <summary>
        /// Get medicine group of a medicine
        /// </summary>
        /// <param name="medId">Medicine Group Id</param>
        /// <returns></returns>
        public MedGroup getMedGroup(int medGroupId)
        {
            string query = "select * from " + Common.PHAN_NHOM_THUOC +
                " where medicinegroupid= " + medGroupId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                MedGroup medGroup = new MedGroup();
                while (sqlDataReader.Read())
                {
                    medGroup.setMedGroupId(sqlDataReader.GetInt32(0));
                    medGroup.setMedGroupName(sqlDataReader.GetString(1));
                    medGroup.setMedGroupNote(sqlDataReader.GetString(2));
                }
                close();
                return medGroup;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isMedGroupExist(int medGroupId)
        {
            string query = "select top 1 medicinegroupid from " + Common.PHAN_NHOM_THUOC +
                " where medicinegroupid = " + medGroupId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        public bool isMedGroupNameExist(string name)
        {
            string query = "select medicinegroupname from " + Common.PHAN_NHOM_THUOC +
                " where medicinegroupname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        /////////////////////////////////////////
        //
        // Route of admin
        //
        /////////////////////////////////////////

        public bool addRouteOfAdmin(RouteOfAdmin roa)
        {
            string query = "insert into " + Common.DUONG_DUNG +
                " values(@routeofadminname)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@routeofadminname", roa.getMedRouteOfAdminName());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }

        }
        public RouteOfAdmin[] getAllRouteOfAdmin()
        {
            string query = "select * from " + Common.DUONG_DUNG;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<RouteOfAdmin> liMedGroup = new List<RouteOfAdmin>();
                RouteOfAdmin medGroup = null;
                while (sqlDataReader.Read())
                {
                    medGroup = new RouteOfAdmin();
                    medGroup.setMedRouteOfAdminId(sqlDataReader.GetInt32(0));
                    medGroup.setMedRouteOfAdminName(sqlDataReader.GetString(1));
                    liMedGroup.Add(medGroup);
                }
                close();
                return liMedGroup.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public RouteOfAdmin getRouteOfAdmin(int roaId)
        {
            string query = "select * from " + Common.DUONG_DUNG +
                " where routeofadminid= " + roaId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                RouteOfAdmin roa = new RouteOfAdmin();
                while (sqlDataReader.Read())
                {
                    roa.setMedRouteOfAdminId(sqlDataReader.GetInt32(0));
                    roa.setMedRouteOfAdminName(sqlDataReader.GetString(1));
                }
                close();
                return roa;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isRouteOfAdminExist(int roaId)
        {
            string query = "select routeofadminid from " + Common.DUONG_DUNG +
                " where routeofadminid = " + roaId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isROANameExist(string name)
        {
            string query = "select routeofadminname from " + Common.DUONG_DUNG +
                " where routeofadminname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        /////////////////////////////////////////
        //
        // Medicine Detail
        //
        /////////////////////////////////////////

        public bool addMedicineDetail(MedicineDetail medDetail)
        {
            bool canAdd = isMedExist(medDetail.getMedId());
            if (!canAdd)
                return false;
            canAdd = isManufacturerExist(medDetail.getManufacturerId());
            if (!canAdd)
                return false;
            string query = "insert into " + Common.CHI_TIET_THUOC +
                " values(@medicineid,@manufacturerid,@importdate,@productdate,@expirationdate, @remainingquantity, @cost, @detailstatus)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@medicineid", medDetail.getMedId());
            sqlCommand.Parameters.AddWithValue("@manufacturerid", medDetail.getManufacturerId());
            sqlCommand.Parameters.AddWithValue("@importdate", medDetail.getImportDate());
            sqlCommand.Parameters.AddWithValue("@productdate", medDetail.getProductDate());
            sqlCommand.Parameters.AddWithValue("@expirationdate", medDetail.getExpirationDate());
            sqlCommand.Parameters.AddWithValue("@remainingquantity", medDetail.getRemainingQuantity());
            sqlCommand.Parameters.AddWithValue("@cost", medDetail.getCost());
            sqlCommand.Parameters.AddWithValue("@detailstatus", medDetail.getStatus());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }

        }

        public bool isMedExist(int medId)
        {
            string query = "select medicineid from " + Common.THUOC +
                " where medicineid = " + medId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isManufacturerExist(int manuId)
        {
            string query = "select manufacturerid from " + Common.NHA_SAN_XUAT_THUOC +
                " where manufacturerid = " + manuId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public MedicineDetail[] getMedDetailByMedId(int medId)
        {
            string query = "select * from " + Common.CHI_TIET_THUOC +
                " where medicineid = " + medId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<MedicineDetail> liMedDetail = new List<MedicineDetail>();
                MedicineDetail medDetail = null;
                DateTime importDate;
                DateTime productDate;
                DateTime expirationDate;
                while (sqlDataReader.Read())
                {
                    medDetail = new MedicineDetail();
                    medDetail.setMedDetailId(sqlDataReader.GetInt32(0));
                    medDetail.setMedId(sqlDataReader.GetInt32(1));
                    medDetail.setManufacturerId(sqlDataReader.GetInt32(2));

                    importDate = sqlDataReader.GetDateTime(3);
                    medDetail.setImportDate(Utils.convertDateTimeToCMDateTime(importDate));

                    productDate = sqlDataReader.GetDateTime(4);
                    medDetail.setProductDate(Utils.convertDateTimeToCMDateTime(productDate));

                    expirationDate = sqlDataReader.GetDateTime(5);
                    
                    medDetail.setExpirationDate(Utils.convertDateTimeToCMDateTime(expirationDate));
                    medDetail.setRemainingQuantity(sqlDataReader.GetInt32(6));
                    medDetail.setCost(sqlDataReader.GetInt32(7));
                    medDetail.setStatus(sqlDataReader.GetInt16(8));
                    liMedDetail.Add(medDetail);
                }
                close();
                return liMedDetail.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        public MedicineDetail getMedDetailByMedDetailId(int medDetailId)
        {
            string query = "select * from " + Common.CHI_TIET_THUOC +
                " where medicinedetailid = " + medDetailId;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                MedicineDetail medDetail = new MedicineDetail();
                DateTime importDate;
                DateTime productDate;
                DateTime expirationDate;
                while (sqlDataReader.Read())
                {
                    medDetail.setMedDetailId(sqlDataReader.GetInt32(0));
                    medDetail.setMedId(sqlDataReader.GetInt32(1));
                    medDetail.setManufacturerId(sqlDataReader.GetInt32(2));

                    importDate = sqlDataReader.GetDateTime(3);
                    medDetail.setImportDate(Utils.convertDateTimeToCMDateTime(importDate));

                    productDate = sqlDataReader.GetDateTime(4);
                    medDetail.setProductDate(Utils.convertDateTimeToCMDateTime(productDate));

                    expirationDate = sqlDataReader.GetDateTime(5);

                    medDetail.setExpirationDate(Utils.convertDateTimeToCMDateTime(expirationDate));
                    medDetail.setRemainingQuantity(sqlDataReader.GetInt32(6));
                    medDetail.setCost(sqlDataReader.GetInt32(7));
                    medDetail.setStatus(sqlDataReader.GetInt16(8));
                }
                close();
                return medDetail;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        public void copyMedicineDetail(MedicineDetail[] medDes, int toMed)
        {
            MedicineDetail med = new MedicineDetail();
            for(int i = 0; i < medDes.Length;i++)
            {
                med = medDes[i];
                med.setMedId(toMed);
                addMedicineDetail(med);
            }
        }
    }

}
